﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for AwardDAO
/// </summary>
public class AwardDAO: DBAccess , IAward
{
    #region sql_commands
    private string SQL_SELECT_ALL = "select * from Awards";
    private string SQL_SELECT_BY_ID = "select * from Awards where AwardID = @awardId";
    private string SQL_INSERT_AWARD = "insert into Awards(AwardName, Prize, CompetitionID) values(@awardName, @prize, @competitionId)";
    private string SQL_UPDATE_AWARD = "update Awards set AwardName = @awardName, Prize = @prize, CompetitionID = @competitionId where AwardID = @awardId";
    private string SQL_DELETE_AWARD = "delete from Awards where CompetitionID = @competitionId";
    private string SQL_AWARD_OF_EXHIBITION = "select a.AwardID, a.AwardName, a.Prize from Awards as a join Competitions as c"
        + "on a.CompetitionID = c.CompetitionID where c.CompetitionID = @competitionId";

    private string SQL_AWARD_OF_COMPETITIONS = "select * from Awards where CompetitionID = @competitionId";
    string message;
    #endregion

    public AwardDAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}


    #region IAward Members

    /// <summary>
    /// Get all awards
    /// </summary>
    /// <returns>IList</returns>
    public IList<AwardInfo> GetAllAward()
    {
        IList<AwardInfo> awardList = new List<AwardInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    AwardInfo award = new AwardInfo();
                    award.AwardId = reader.GetInt32(0);
                    award.AwardName = reader.GetString(1);
                    award.Prize = reader.GetString(2);
                    award.CompetitionId = reader.GetInt32(3);

                    awardList.Add(award);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return awardList;
    }


    /// <summary>
    /// Get award by id
    /// </summary>
    /// <param name="awardId"></param>
    /// <returns>AwardInfo</returns>
    public AwardInfo GetAwardById(int awardId)
    {
        AwardInfo award = new AwardInfo();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_BY_ID, conn);
            cmd.Parameters.AddWithValue("@awardId", awardId);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    award.AwardId = reader.GetInt32(0);
                    award.AwardName = reader.GetString(1);
                    award.Prize = reader.GetString(2);
                    award.CompetitionId = reader.GetInt32(3);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return award;

    }

    /// <summary>
    /// Insert award
    /// </summary>
    /// <param name="award"></param>
    /// <returns>int</returns>
    public int InsertAward(AwardInfo award)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_INSERT_AWARD, conn);
            cmd.Parameters.AddWithValue("@awardName", award.AwardName);
            cmd.Parameters.AddWithValue("@prize", award.Prize);
            cmd.Parameters.AddWithValue("@competitionId", award.CompetitionId);          
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    /// <summary>
    /// Update award
    /// </summary>
    /// <param name="award"></param>
    /// <returns>int</returns>
    public int UpdateAward(AwardInfo award)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE_AWARD, conn);
            cmd.Parameters.AddWithValue("@awardName", award.AwardName);
            cmd.Parameters.AddWithValue("@prize", award.Prize);
            cmd.Parameters.AddWithValue("@competitionId", award.CompetitionId);
            cmd.Parameters.AddWithValue("@awardId", award.AwardId);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    /// <summary>
    /// Delete award
    /// </summary>
    /// <param name="awardId"></param>
    /// <returns>int</returns>
    public int DeleteAward(int competitionID)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE_AWARD, conn);
            cmd.Parameters.AddWithValue("@competitionId", competitionID);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }


    /// <summary>
    /// Awards of competition was hold
    /// </summary>
    /// <param name="competitionId"></param>
    /// <returns>dataset</returns>
    public DataSet AwardOfCompetition(int competitionId)
    {
        DataSet ds = new DataSet();
        int a = 0;
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_AWARD_OF_COMPETITIONS, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@competitionId", competitionId);
        adapter.Fill(ds, "AwardOfCompetition");
        return ds;
    }  
    #endregion

    /// <summary>
    /// Get Award of Competition
    /// </summary>
    /// <returns>IList</returns>
    public IList<AwardInfo> GetAwardOfCompetition(int competitionId)
    {
        IList<AwardInfo> awardList = new List<AwardInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_AWARD_OF_COMPETITIONS, conn);
            cmd.Parameters.AddWithValue("@competitionId", competitionId);  
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    AwardInfo award = new AwardInfo();
                    award.AwardId = reader.GetInt32(0);
                    award.AwardName = reader.GetString(1);
                    award.Prize = reader.GetString(2);
                    award.CompetitionId = reader.GetInt32(3);

                    awardList.Add(award);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return awardList;
    }


  
}